package com.sq.partner.manager.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Component;

import com.sq.partner.manager.common.exception.AppException;
import com.sq.partner.manager.controller.AgentInfoMtController;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.StrUtil;

/**
 * 
 * @author zf
 *
 */
@Component
public class ImportExcelBuilderTool {
	
	/**
	 * excel解析
	 * @param inputStream
	 * @param entity
	 * @return
	 * @throws Exception
	 */
	public List<Map<String,Object>> parseExcel(InputStream inputStream,ImportExcelEntity entity) throws Exception{
		if(inputStream==null||entity==null||entity.getColumnName()==null||entity.getColumnValidateRule()==null) 
			throw new AppException("上传失败，上传文件有误");
		HSSFWorkbook workBook = null;
		try {
			workBook = new HSSFWorkbook(inputStream);
		} catch (IOException e) {
			throw new AppException("上传失败，上传文件有误");
		}
		String[] columnNames = entity.getColumnName().split(",");
		String[] columnValidateRules = entity.getColumnValidateRule().split(",");
		if(columnNames.length != columnValidateRules.length){
			throw new AppException("上传失败，校验规则有误");
		}
		HSSFSheet sheet = workBook.getSheetAt(0);
		int lastRowNumber = sheet.getLastRowNum();
		if(lastRowNumber<entity.getBeginRow()){
			throw new AppException("上传失败，上传文件内容为空");
		}
		List<Map<String,Object>> lists= new ArrayList<Map<String,Object>>();
		Map<String,Object> map;
		HSSFRow row;
		Object cellText;
		int rule;
		String excelErrorMes = "";
		boolean norepeatFlag = false;//不重复校验标志
		String norepeatString = "";
		int MaxRow = 9999;
		Map<String,String> repeatMap  = new HashMap<String,String>();
		List<String> repeatList = new ArrayList<String>();
		if(entity.getExtraValidateRule()!=null){
			String[] extraRules = entity.getExtraValidateRule().split(","); 
			for(String extraStr : extraRules){
				if(extraStr.startsWith("20_")){
					norepeatFlag = true;
					norepeatString = extraStr.substring(2);
				}else if(extraStr.startsWith("21_")){
					MaxRow = Integer.parseInt(extraStr.substring(3, extraStr.length()-1));
				}
			}
		}
		String repeatKey = "";
		for(int rowNum=entity.getBeginRow();rowNum<=lastRowNumber;rowNum++){
			row = sheet.getRow(rowNum);
			if(row!=null&&!ExcelTool.checkAllRowNull(row,columnNames.length)){
				map = new HashMap<String,Object>();
				if(norepeatFlag) repeatKey = "";
				for(int colNum = 0; colNum<columnNames.length; colNum++){
					cellText = ExcelTool.getCell(row.getCell(colNum));
					rule = Integer.parseInt(columnValidateRules[colNum]);
					boolean checkFlag = ExcelTool.checkCell(rule,cellText);
					if(checkFlag){
						map.put(columnNames[colNum], cellText);
					}else{
						excelErrorMes += "第"+(rowNum+1)+"行,第"+(colNum+1)+"列"+ExcelTool.getErrorMessage(rule)+"<br/>";
					}
					if(norepeatFlag&&norepeatString.contains("_"+colNum+"_")) repeatKey += String.valueOf(cellText);
				}
				lists.add(map);
				if(lists.size()>MaxRow) throw new AppException(ExcelTool.getErrorMessage(21)+MaxRow);
				if(norepeatFlag){
					if(repeatMap.get(repeatKey)==null){
						repeatMap.put(repeatKey, rowNum+1+"");
					}else{
						repeatMap.put(repeatKey, repeatMap.get(repeatKey)+","+(rowNum+1));
						if(!repeatList.contains(repeatKey)) repeatList.add(repeatKey);
					}
				}
			}
		}
		if(norepeatFlag){
			Iterator<String> ita = repeatList.iterator();
			while(ita.hasNext()){
				excelErrorMes += "第"+repeatMap.get(ita.next())+"行"+ExcelTool.getErrorMessage(20)+"\n";
			}		
		}
		if(StrUtil.isNotEmpty(excelErrorMes)) throw new AppException(excelErrorMes);
		return lists;
	}	
	
	public static void main(String[] args) {
		ImportExcelBuilderTool t = new ImportExcelBuilderTool();
		try {
			ImportExcelEntity entity= new ImportExcelEntity();
			entity.setColumnName("name,id_name,id_code,birth,sex,tel");
			entity.setColumnValidateRule("1,1,3,4,1,2");
			entity.setBeginRow(2);
			entity.setExtraValidateRule("20_2_");
			List<Map<String,Object>> lists= t.parseExcel(new FileInputStream(new File("C:\\Users\\Mango\\Downloads\\insured1.xls")), entity);
			for(Map<String,Object> map : lists){
				System.out.println(map.toString());
			}
		} catch (AppException e) {
			System.out.println("====================自定义异常");
			System.out.println(e.getMessage());
		} catch (Exception e) {
			System.out.println("====================系统异常");
			System.out.println(e.getMessage());
		}
	}
}
